﻿/*
#------------------------------------------------------------------------------
#-- Program Name:	[dbo].[tblMSX_server_sql_info]
#-- Purpose:		Stores SQL Server specific information about servers.
#--	Last Update:	04/28/2016
#--					For a complete history - please review comments in Version
#--					Control.
#------------------------------------------------------------------------------
*/
CREATE TABLE [dbo].[tblMSX_server_sql_info]
(
	[sql_ssd_id]					INT								NOT NULL,
	[sql_last_update]				DATETIME						NOT NULL,
	[sql_centraldba_db_added]		DATETIME						NULL,
	[sql_master_jobs_added]			DATETIME						NULL,
	[sql_is_msx_instance]			BIT								NOT NULL DEFAULT ((0)),
	[sql_engine_edition]			INT								NULL,
	[sql_edition_id]				INT								NULL,
	[sql_product_version]			VARCHAR(25)						NULL,
	[sql_product_level]				VARCHAR(5)						NULL,
	[sql_has_master_jobs]			BIT								NOT NULL DEFAULT ((0)),
	[sql_has_local_jobs]			BIT								NOT NULL DEFAULT ((0)),
	[sql_backup_directory]			VARCHAR(255)					NULL,
	[sql_is_mixed_mode]				BIT								NOT NULL DEFAULT ((0)),
	[sql_has_centraldba_db]			BIT								NOT NULL DEFAULT ((0)),
	[sql_edition_ro]				AS ([dbo].[fnMSX_get_sql_edition]([sql_edition_id])),
	[sql_version_major_ro]			AS ([dbo].[fnMSX_get_version]([sql_product_version], (1))),
	[sql_version_minor_ro]			AS ([dbo].[fnMSX_get_version]([sql_product_version], (0))),
	[sql_backup_server_ro]			AS ([dbo].[fnMSX_get_backup_server]([sql_backup_directory])),
	CONSTRAINT [PK_tblMSX_server_sql_info] PRIMARY KEY NONCLUSTERED ([sql_ssd_id] ASC), 
	CONSTRAINT [FK_tblMSX_server_sql_info_tblMSX_server_discovery] FOREIGN KEY ([sql_ssd_id]) REFERENCES [tblMSX_server_discovery] ([ssd_id]) 
)
